package org.wheel.plugins.toolkit.jdbc.pool.util.excution;

import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.bean.copier.CopyOptions;
import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.ClassUtil;
import cn.hutool.core.util.ReflectUtil;
import cn.hutool.json.JSONConfig;
import cn.hutool.json.JSONUtil;
import cn.hutool.log.Log;
import cn.hutool.log.LogFactory;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.wheel.plugins.toolkit.jdbc.pool.bean.DatabaseConnection;
import org.wheel.plugins.toolkit.jdbc.pool.util.DynamicJdbcPoolUtil;
import org.wheel.plugins.toolkit.jsqlparse.helper.ParamValueConvert;
import org.wheel.plugins.toolkit.sql.core.GlobalConfig;
import org.wheel.plugins.toolkit.sql.enums.SqlModel;
import org.wheel.plugins.toolkit.util.bean.DataHandlerUtil;
import org.wheel.plugins.toolkit.util.bean.DynaBean;
import org.wheel.plugins.toolkit.util.sql.SQLHelper;

import java.lang.reflect.Field;
import java.util.*;
import java.util.stream.Collectors;

/**
 * 查询专属 Executor
 */
public class SqlQueryExecutor {

    private final DynamicJdbcPoolUtil dynamicJdbcPool = DynamicJdbcPoolUtil.getInstance();

    // 直接使用 LogFactory 创建日志对象，Hutool 会自动检测项目中的日志框架（优先级：Slf4j > Log4j > Logback > JDK Logging）
    private static final Log log = LogFactory.get();

    /* ================== Map 返回 ================== */

    public List<Map<String, Object>> queryForList(DatabaseConnection conn,
                                                  String sql,
                                                  LinkedHashMap<String, Object> param,
                                                  SqlModel model) {
        JdbcTemplate tpl = dynamicJdbcPool.getOrCreateJdbcTemplate(conn);
        SqlModelResolver.ResolvedSql rs = SqlModelResolver.resolve(sql, param, model);
        if (rs.getParamSource() != null) {
            NamedParameterJdbcTemplate named = new NamedParameterJdbcTemplate(tpl);
            log.debug("exec SQL: {}，param: {}\nexec SQL: {}", rs.getFinalSql(), param, ParamValueConvert.convertValuesIntoSql(sql, param, GlobalConfig.getDialect()));
            return named.queryForList(rs.getFinalSql(), rs.getParamSource());
        }
        log.debug("exec SQL: {}，param: {}\nexec SQL: {}", rs.getFinalSql(), param, ParamValueConvert.convertValuesIntoSql(sql, param, GlobalConfig.getDialect()));
        return tpl.queryForList(rs.getFinalSql(), rs.getArgs());
    }

    public Map<String, Object> queryForMap(DatabaseConnection conn,
                                           String sql,
                                           LinkedHashMap<String, Object> param,
                                           SqlModel model) {
        JdbcTemplate tpl = dynamicJdbcPool.getOrCreateJdbcTemplate(conn);
        SqlModelResolver.ResolvedSql rs = SqlModelResolver.resolve(sql, param, model);
        try {
            if (rs.getParamSource() != null) {
                NamedParameterJdbcTemplate named = new NamedParameterJdbcTemplate(tpl);
                log.debug("exec SQL: {}，param: {}\nexec SQL: {}", rs.getFinalSql(), param, ParamValueConvert.convertValuesIntoSql(sql, param, GlobalConfig.getDialect()));
                return named.queryForMap(rs.getFinalSql(), rs.getParamSource());
            }
            log.debug("exec SQL: {}，param: {}\nexec SQL: {}", rs.getFinalSql(), param, ParamValueConvert.convertValuesIntoSql(sql, param, GlobalConfig.getDialect()));
            return tpl.queryForMap(rs.getFinalSql(), rs.getArgs());
        } catch (org.springframework.dao.EmptyResultDataAccessException e) {
            return null;
        }
    }

    /* ================== Bean 返回 ================== */

    public <T> List<T> queryForList(DatabaseConnection conn,
                                    String sql,
                                    LinkedHashMap<String, Object> param,
                                    SqlModel model,
                                    Class<T> clazz) {
        if(SQLHelper.hasNonBasicNestedProperty(sql, clazz)){
            List<Map<String, Object>> mapList = queryForList(conn, sql, param, model);
            return buildBeanList(mapList, clazz);
        }else{
            JdbcTemplate tpl = dynamicJdbcPool.getOrCreateJdbcTemplate(conn);
            SqlModelResolver.ResolvedSql rs = SqlModelResolver.resolve(sql, param, model);
            if (rs.getParamSource() != null) {
                NamedParameterJdbcTemplate named = new NamedParameterJdbcTemplate(tpl);
                log.debug("exec SQL: {}，param: {}\nexec SQL: {}", rs.getFinalSql(), param, ParamValueConvert.convertValuesIntoSql(sql, param, GlobalConfig.getDialect()));
                return named.query(rs.getFinalSql(), rs.getParamSource(), new BeanPropertyRowMapper<>(clazz));
            }
            log.debug("exec SQL: {}，param: {}\nexec SQL: {}", rs.getFinalSql(), param, ParamValueConvert.convertValuesIntoSql(sql, param, GlobalConfig.getDialect()));
            return tpl.query(rs.getFinalSql(), new BeanPropertyRowMapper<>(clazz), rs.getArgs());
        }
    }

    public <T> T queryForObject(DatabaseConnection conn,
                                String sql,
                                LinkedHashMap<String, Object> param,
                                SqlModel model,
                                Class<T> clazz) {
        // 有嵌套属性，有可能关联后，查询结果是多条纪录，需要合并后才能成为一条纪录
        if(SQLHelper.hasNonBasicNestedProperty(sql, clazz)) {
//            Map<String, Object> map = queryForMap(conn, sql, param, model);
            List<Map<String, Object>> mapList = queryForList(conn, sql, param, model);
            if(CollUtil.isNotEmpty(mapList)){
                if(mapList.size() == 1){
                    return buildBean(mapList.get(0), clazz);
                }else{
                    List<T> list = buildBeanList(mapList, clazz);
                    if(CollUtil.isEmpty(list)){
                        return null;
                    }
                    return list.get(0);
                }
            }
            return null;
        } else{
            JdbcTemplate tpl = dynamicJdbcPool.getOrCreateJdbcTemplate(conn);
            SqlModelResolver.ResolvedSql rs = SqlModelResolver.resolve(sql, param, model);
            try {
                if (ClassUtil.isBasicType(clazz)) {
                    // 基本类型走 queryForObject
                    if (rs.getParamSource() != null) {
                        NamedParameterJdbcTemplate named = new NamedParameterJdbcTemplate(tpl);
                        log.debug("exec SQL: {}，param: {}\nexec SQL: {}", rs.getFinalSql(), param, ParamValueConvert.convertValuesIntoSql(sql, param, GlobalConfig.getDialect()));
                        return named.queryForObject(rs.getFinalSql(), rs.getParamSource(), clazz);
                    }
                    log.debug("exec SQL: {}，param: {}\nexec SQL: {}", rs.getFinalSql(), param, ParamValueConvert.convertValuesIntoSql(sql, param, GlobalConfig.getDialect()));
                    return tpl.queryForObject(rs.getFinalSql(), clazz, rs.getArgs());
                } else {
                    // Bean 类型
                    if (rs.getParamSource() != null) {
                        NamedParameterJdbcTemplate named = new NamedParameterJdbcTemplate(tpl);
                        log.debug("exec SQL: {}，param: {}\nexec SQL: {}", rs.getFinalSql(), param, ParamValueConvert.convertValuesIntoSql(sql, param, GlobalConfig.getDialect()));
                        return named.queryForObject(rs.getFinalSql(), rs.getParamSource(), new BeanPropertyRowMapper<>(clazz));
                    }
                    log.debug("exec SQL: {}，param: {}\nexec SQL: {}", rs.getFinalSql(), param, ParamValueConvert.convertValuesIntoSql(sql, param, GlobalConfig.getDialect()));
                    return tpl.queryForObject(rs.getFinalSql(), new BeanPropertyRowMapper<>(clazz), rs.getArgs());
                }
            } catch (org.springframework.dao.EmptyResultDataAccessException e) {
                return null;
            }
        }
    }

    public Long executeCountSqlQuery(DatabaseConnection conn,
                                     String countSql,
                                     LinkedHashMap<String, Object> params,
                                     SqlModel model) {
        return queryForObject(conn, countSql, params, model, Long.class);
    }

    /* ================== 原生数组参数（兼容旧代码） ================== */

    public List<Map<String, Object>> nativeQueryForList(DatabaseConnection conn, String sql, Object[] args) {
        JdbcTemplate tpl = dynamicJdbcPool.getOrCreateJdbcTemplate(conn);
        log.debug("exec SQL: {}，param: {}\nexec SQL: {}", sql, args, ParamValueConvert.convertValuesIntoSql(sql, args, GlobalConfig.getDialect()));
        return tpl.queryForList(sql, args);
    }

    public Map<String, Object> nativeQueryForMap(DatabaseConnection conn, String sql, Object[] args) {
        JdbcTemplate tpl = dynamicJdbcPool.getOrCreateJdbcTemplate(conn);
        try {
            log.debug("exec SQL: {}，param: {}\nexec SQL: {}", sql, args, ParamValueConvert.convertValuesIntoSql(sql, args, GlobalConfig.getDialect()));
            return tpl.queryForMap(sql, args);
        } catch (org.springframework.dao.EmptyResultDataAccessException e) {
            return null;
        }
    }

    public <T> List<T> nativeQueryForList(DatabaseConnection conn, String sql, Object[] args, Class<T> clazz) {
        if(SQLHelper.hasNonBasicNestedProperty(sql, clazz)){
            List<Map<String, Object>> mapList = nativeQueryForList(conn, sql, args);
            return buildBeanList(mapList, clazz);
        }else {
            JdbcTemplate tpl = dynamicJdbcPool.getOrCreateJdbcTemplate(conn);
            if (ClassUtil.isBasicType(clazz)) {
                log.debug("exec SQL: {}，param: {}\nexec SQL: {}", sql, args, ParamValueConvert.convertValuesIntoSql(sql, args, GlobalConfig.getDialect()));
                return tpl.queryForList(sql, clazz, args);
            }
            log.debug("exec SQL: {}，param: {}\nexec SQL: {}", sql, args, ParamValueConvert.convertValuesIntoSql(sql, args, GlobalConfig.getDialect()));
            return tpl.query(sql, new BeanPropertyRowMapper<>(clazz), args);
        }
    }

    public <T> T nativeQueryForObject(DatabaseConnection conn, String sql, Object[] args, Class<T> clazz) {
        if(SQLHelper.hasNonBasicNestedProperty(sql, clazz)) {
            Map<String, Object> map = nativeQueryForMap(conn, sql, args);
            return buildBean(map, clazz);
        } else {
            JdbcTemplate tpl = dynamicJdbcPool.getOrCreateJdbcTemplate(conn);
            try {
                if (ClassUtil.isBasicType(clazz)) {
                    log.debug("exec SQL: {}，param: {}\nexec SQL: {}", sql, args, ParamValueConvert.convertValuesIntoSql(sql, args, GlobalConfig.getDialect()));
                    return tpl.queryForObject(sql, clazz, args);
                }
                log.debug("exec SQL: {}，param: {}", sql, args, ParamValueConvert.convertValuesIntoSql(sql, args, GlobalConfig.getDialect()));
                return tpl.queryForObject(sql, new BeanPropertyRowMapper<>(clazz), args);
            } catch (org.springframework.dao.EmptyResultDataAccessException e) {
                return null;
            }
        }
    }

    // 动态构建对象，可以进行对象数据合并
    private <T> T buildBean(Map<String, Object> map, Class<T> clazz){
        List<DynaBean> dBean = DataHandlerUtil.getInstance().buildDynamicObject(List.of(map));
        if(CollUtil.isEmpty(dBean)){
            return null;
        }
        List<DynaBean> resultList = DataHandlerUtil.getInstance().buildDynamicObjectGroup(dBean, (key)->isNestedPropertyCollection(clazz,  key));
        if(resultList.isEmpty()){
            return null;
        }
        return BeanUtil.toBean(resultList.get(0), clazz, CopyOptions.create().ignoreNullValue());
//        return BeanUtil.toBean(deepClean(resultList.get(0)), clazz, CopyOptions.create().ignoreNullValue());
    }
    // 批量动态构建对象
    private <T> List<T> buildBeanList(List<Map<String, Object>> mapList, Class<T> clazz){
        List<DynaBean> dBean = DataHandlerUtil.getInstance().buildDynamicObject(mapList);
        if(CollUtil.isEmpty(dBean)){
            return null;
        }
        List<DynaBean> resultList = DataHandlerUtil.getInstance().buildDynamicObjectGroup(dBean, (key)->isNestedPropertyCollection(clazz,  key));

        return resultList.stream().map(bean->BeanUtil.toBean(bean, clazz, CopyOptions.create().ignoreNullValue())).collect(Collectors.toList());
//        return resultList.stream().map(bean->BeanUtil.toBean(deepClean(bean), clazz, CopyOptions.create().ignoreNullValue())).collect(Collectors.toList());
    }

    /**
     * 递归清理空的嵌套结构
     */
    public static Object deepClean(Object obj) {
        if (obj == null) {
            return null;
        }

        // 处理 Map 类型
        if (obj instanceof Map<?, ?> originalMap) {
            Map<Object, Object> cleanedMap = new LinkedHashMap<>();

            for (Map.Entry<?, ?> entry : originalMap.entrySet()) {
                Object cleanedValue = deepClean(entry.getValue());
                if (cleanedValue != null) {
                    cleanedMap.put(entry.getKey(), cleanedValue);
                }
            }

            // 如果清理后 Map 为空，则返回 null
            if (cleanedMap.isEmpty() || cleanedMap.values().stream().allMatch(Objects::isNull)) {
                return null;
            }
            return cleanedMap;
        }
        // 处理 Collection 类型
        if (obj instanceof Collection<?> originalCollection) {
            List<Object> cleanedCollection = new ArrayList<>();

            for (Object item : originalCollection) {
                Object cleanedItem = deepClean(item);
                if (cleanedItem != null) {
                    cleanedCollection.add(cleanedItem);
                }
            }

            // 如果清理后集合为空，则返回 null
            if (cleanedCollection.isEmpty()) {
                return null;
            }
            return cleanedCollection;
        }
        // 其他类型直接返回
        return obj;
    }

    /**
     * 判断嵌套属性是否是集合类型
     * @param targetClass 目标类
     * @param nestedPropertyName 属性名称 字符串，嵌套属性用.分隔
     * @return
     */
    private boolean isNestedPropertyCollection(Class<?> targetClass, String nestedPropertyName) {
        String[] names = nestedPropertyName.split("\\.");
        Class<?> current = targetClass;
        for (int i = 0; i < names.length; i++) {
            java.lang.reflect.Field field = cn.hutool.core.util.ReflectUtil.getField(current, names[i]);
            if (field == null) return false;
            if (i == names.length - 1) {
                return java.util.Collection.class.isAssignableFrom(field.getType());
            }
            current = field.getType();
        }
        return false;
    }
}